package com.seio.edc.util;

import com.nova.edc.model.util.ExcelArea;
import com.nova.frame.utils.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @param
 * @author <a href="mailto:huzhanjun@novacloud.com">huzj</a>
 * @version : 1.0
 * @date 2016-01-15 17:20
 */
public class ExcelUtil {

    public static List<ExcelArea> excelToArea(MultipartFile file) {
        List<ExcelArea> list = new ArrayList<ExcelArea>();
        try {
            //FileInputStream fileInputStream = new FileInputStream(file);
            InputStream fileInputStream = file.getInputStream();
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            for (int i = 2; i < (xssfSheet.getLastRowNum() + 1); i++) {
                XSSFRow xssfRow = xssfSheet.getRow(i);
                // 根据得到的行信息，配置到model类里面
                ExcelArea excelArea = new ExcelArea();
                String companycode = getRealCellValue(xssfSheet, xssfRow.getCell(0));
                if (StringUtils.isNotNullOrEmpty(companycode)) {
                    excelArea.setCompanycode(Integer.parseInt(companycode));
                }
                String customername = getRealCellValue(xssfSheet, xssfRow.getCell(1));
                excelArea.setCustomername(customername);
                String gatewayid = getRealCellValue(xssfSheet, xssfRow.getCell(2));
                excelArea.setGatewayid(gatewayid);
                String sensorid = getRealCellValue(xssfSheet, xssfRow.getCell(3));
                if (StringUtils.isNotNullOrEmpty(sensorid)) {
                    excelArea.setSensorid(Integer.parseInt(sensorid));
                }
                String channelid = getRealCellValue(xssfSheet, xssfRow.getCell(4));
                if (StringUtils.isNotNullOrEmpty(channelid.trim())) {
                    excelArea.setChannelid(Integer.parseInt(channelid));
                }
                String devicetypename = getRealCellValue(xssfSheet, xssfRow.getCell(5));
                excelArea.setDevicetypename(devicetypename);
                String areaname = getRealCellValue(xssfSheet, xssfRow.getCell(6));
                excelArea.setAreaname(areaname);
                String tralightname = getRealCellValue(xssfSheet, xssfRow.getCell(7));
                excelArea.setTralightname(tralightname);
                String tralightcode = getRealCellValue(xssfSheet, xssfRow.getCell(8));
                excelArea.setTralightcode(tralightcode);
                String trapower = getRealCellValue(xssfSheet, xssfRow.getCell(9));
                if (StringUtils.isNotNullOrEmpty(trapower.trim())) {
                    excelArea.setTrapower(Integer.parseInt(trapower));
                }
                String tranum = getRealCellValue(xssfSheet, xssfRow.getCell(10));
                if (StringUtils.isNotNullOrEmpty(tranum.trim())) {
                    excelArea.setTranum(Integer.parseInt(tranum));
                }
                String ledlightname = getRealCellValue(xssfSheet, xssfRow.getCell(11));
                excelArea.setLedlightname(ledlightname);
                String ledlightcode = getRealCellValue(xssfSheet, xssfRow.getCell(12));
                excelArea.setLedlightcode(ledlightcode);
                String ledpower = getRealCellValue(xssfSheet, xssfRow.getCell(13));
                if (StringUtils.isNotNullOrEmpty(ledpower.trim())) {
                    excelArea.setLedpower(Integer.parseInt(ledpower));
                }
                String lednum = getRealCellValue(xssfSheet, xssfRow.getCell(14));
                if (StringUtils.isNotNullOrEmpty(lednum.trim())) {
                    excelArea.setLednum(Integer.parseInt(lednum));
                }
                String reallighttype = getRealCellValue(xssfSheet, xssfRow.getCell(15));
                if (StringUtils.isNotNullOrEmpty(reallighttype.trim())) {
                    excelArea.setReallighttype(Integer.parseInt(reallighttype));
                }
                list.add(excelArea);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

//    public static void main(String[] args) {
//        ExcelUtil excelUtil = new ExcelUtil();
//        List<ExcelArea> list = excelUtil.excelToArea(new File("E:\\i_workspace\\data.xlsx"));
//        System.out.println(list.size());
//    }

    /**
     * 获取单元格的值
     */
    public static String getRealCellValue(XSSFSheet sheet, XSSFCell cell) {
        if (isMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex())) {
            return getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex());
        } else {
            return getCellValue(cell);
        }
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(XSSFSheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    XSSFRow fRow = sheet.getRow(firstRow);
                    XSSFCell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return "";
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {

                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(XSSFCell cell) {

        if (cell == null) return "";

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

            return cell.getCellFormula();

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            return cell.getStringCellValue();
        }

        return "";
    }

}
